package ApplicationsOld;
import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import AdminController.AdminExe.*;
import ApplicationsYoung.ApplicationsYoungBean;
import AdminController.AdminExe.AdminExeBean;
import Connect.connector;

public class ApplicationsOldDAO {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public int babyPlusInquiry(int idnumber){
		conn = connector.getConnection();
		int num = 0;
		try{
			String sql ="select * from OLDPLUS where Members_ID = ?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,idnumber);
			rs = pstmt.executeQuery();
			while(rs.next()){
				num = rs.getInt("Old_Check");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return num;
	}
	
	public void MappingDieY(int idnumber){
		conn = connector.getConnection();
		
		try{
			String sql ="delete  from ADMINCHECKY where Mem_Id = ?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,idnumber);
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	
	}
	
	public void dieMappingY(int idnumber){
		conn = connector.getConnection();
		
		try{
			String sql ="delete from MAPPING where Map_Young = ?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,idnumber);
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	
	}
	
	
	public String cityCheck (int id){
		conn = connector.getConnection();
		String address = "";
		try{
			
			String sql="select Mem_Address1 from MEMBERS where ID=?";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,id);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				 address = rs.getString(1);
			}
			 return address;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return null;
	}
	
	
	
		public int abliltyCheck (ApplicationsOldBean oldBean,int id){
			conn = connector.getConnection();
			int sum = 0;
			String Vvalue = "";
			try{
				
				String sql="insert into ABLILTY(Vvalue,Members_Id)values(?,?)";
				
				pstmt = conn.prepareStatement(sql);
				for(int i = 0 ; i<oldBean.getVvalue().length ; i++){
					
					if(oldBean.getVvalue()[i].equals("保育教師")){
						sum += sum+1;
					}else if(oldBean.getVvalue()[i].equals("看護婦")){
						sum = sum+2;
					}else if(oldBean.getVvalue()[i].equals("医者")){
						sum = sum+4;
					}else if(oldBean.getVvalue()[i].equals("幼稚園教師")){
						sum = sum+8;
					}else if(oldBean.getVvalue()[i].equals("小学校教師")){
						sum = sum+16;
					}else if(oldBean.getVvalue()[i].equals("講座卒業")){
						sum = sum+32;
					}else if(oldBean.getVvalue()[i].equals("他の資格")){
						sum = sum+64;
					}
					pstmt.setString(1,oldBean.getVvalue()[i]);				
					pstmt.setInt(2,id);
					pstmt.executeUpdate();
				}
				System.out.println("자격총합:"+sum);
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
			return sum;
		}
		
		public void abliltySum (int sum,int id){
			conn = connector.getConnection();
			
			
			try{
				
				String sql="insert into ABLILTYSUM(Members_Id,Vvalue_Sum)values(?,?)";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,id);
				pstmt.setInt(2,sum);
				pstmt.executeUpdate();
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
		
		public int dayCheck(ApplicationsOldBean oldBean,int id){
			conn = connector.getConnection();
			int Dsum = 0;
			try{
				String sql ="insert into DAYCHECK(Dvalue,Members_Id)values(?,?)";
				
				pstmt = conn.prepareStatement(sql);
				
				for(int i = 0 ; i<oldBean.getDvalue().length ; i++){
					
					if(oldBean.getDvalue()[i].equals("月")){
						Dsum = Dsum+1;
					}else if(oldBean.getDvalue()[i].equals("火")){
						Dsum = Dsum+2;
					}else if(oldBean.getDvalue()[i].equals("水")){
						Dsum = Dsum+4;
					}else if(oldBean.getDvalue()[i].equals("木")){
						Dsum = Dsum+8;
					}else if(oldBean.getDvalue()[i].equals("金")){
						Dsum = Dsum+16;
					}else if(oldBean.getDvalue()[i].equals("土")){
						Dsum = Dsum+32;
					}else if(oldBean.getDvalue()[i].equals("日")){
						Dsum = Dsum+64;
					}else if(oldBean.getDvalue()[i].equals("毎日")){
						Dsum = Dsum+128;
					}
					pstmt.setString(1,oldBean.getDvalue()[i]);				
					pstmt.setInt(2,id);
					pstmt.executeUpdate();
				}
				System.out.println("날짜총합:"+Dsum);
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
			return Dsum;
		}

		public void dayCheckSum (int Dsum,int id){
			conn = connector.getConnection();
			
			
			try{
				
				String sql="insert into DAYCHECKSUM(Members_Id,Dvalue_Sum)values(?,?)";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,id);
				pstmt.setInt(2,Dsum);
				pstmt.executeUpdate();
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
		
		public void oldCheck(ApplicationsOldBean oldBean,int id,String address){
			conn = connector.getConnection();
			try{
				String sql ="insert into OLDPLUS(ID,Members_Id,Old_Add,Old_Kind,Old_Gender,Old_Money,Old_Photo,Old_Date,Old_Check)values(NULL,?,?,?,?,?,?,Sysdate(),1)";
			
				pstmt = conn.prepareStatement(sql);
				
				pstmt.setInt(1,id);
				pstmt.setString(2,address);
				pstmt.setString(3,oldBean.getOld_Kind());
				pstmt.setInt(4,oldBean.getOld_Gender());
				pstmt.setString(5,oldBean.getOld_Money());
				pstmt.setString(6,oldBean.getOld_Photo());
				pstmt.executeUpdate();
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
		public void cancelSet(int idnumber){
			conn = connector.getConnection();
			try{
				
				String sql="UPDATE OLDPLUS SET Old_Check=2 WHERE Members_Id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,idnumber);				
				pstmt.executeUpdate();
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
		
		public int mIdCheck(String id){
			conn = connector.getConnection();
			
			try{
				    
				   String sql="select ID from MEMBERS where Mem_Nick=?";
					
					
					pstmt = conn.prepareStatement(sql);
					
					pstmt.setString(1,id);
					
					rs = pstmt.executeQuery();
					
					while(rs.next()){
						int mId = rs.getInt(1);
						return mId;
					}
			
				}catch(Exception e){
				   e.printStackTrace();
			   }finally{
					connector.releaseConnection(conn, pstmt, rs);
				}
			  return -1;
		}
		
		public void adminOld(String name,int id){
			conn = connector.getConnection();
			try{
				String sql ="insert into ADMINOLD(ID,Mem_Id,Mem_Nick)values(NULL,?,?)";
				
				pstmt = conn.prepareStatement(sql);
				
				pstmt.setInt(1,id);
				pstmt.setString(2,name);
				pstmt.executeUpdate();
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
		
		public void finalacept(int id,int up){
			conn = connector.getConnection();
			try{
				String sql = "update OLDPLUS set Old_Check=? where Members_Id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,up);
				pstmt.setInt(2,id);
				pstmt.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
		}
		public int mapOld(int id){
			conn = connector.getConnection();
			try{
				String sql = "select Map_Old from MAPPING where Map_Young = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,id);
				
				rs = pstmt.executeQuery();
				
				while(rs.next()){
				
				int mId = rs.getInt(1);
				return mId;
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
			return -1;
		}
		
		
		public void finalaceptY(int id,int up){
			conn = connector.getConnection();
			try{
				String sql = "update BABYPLUS set Bby_Check=? where Members_Id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,up);
				pstmt.setInt(2,id);
				pstmt.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
		}
		
		
		public void finalkotoY(int id,int up,int mFup){
			conn = connector.getConnection();
			try{
				System.out.println("아이거 왜안되는데");
				System.out.println("Mem_Check:"+up);
				System.out.println("Map_Check:"+mFup);
				System.out.println("Mem_Id:"+id);
				
				String sql = "update ADMINCHECKY set Mem_Check=? , Map_Check =? where Mem_Id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,up);
				pstmt.setInt(2,mFup);
				pstmt.setInt(3,id);
				pstmt.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
		}
		
		public void finalkotoO(int id,int up){
			conn = connector.getConnection();
			try{
				String sql = "update ADMINCHECKO set Mem_Check=? where Mem_Id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,up);
				pstmt.setInt(2,id);
				pstmt.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
		}
		
		
		public int mapYoung(int id){
			conn = connector.getConnection();
			
			try{
				String sql = "select Map_Young from MAPPING where Map_Old = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,id);
			
				rs = pstmt.executeQuery();
				
				while(rs.next()){
					int young = rs.getInt(1);
					return young;
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
			return -1;
		}
		
		/*
		public void finalDeathY(int id){
			conn = connector.getConnection();
			try{
				String sql = "delete from ADMINCHECKY where Mem_Id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,id);
				pstmt.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
		}
		*/
		
		
		public void finalDeathO(int id){
			conn = connector.getConnection();
			try{
				String sql = "delete from ADMINCHECKO where Mem_Id=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,id);
				pstmt.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt,rs);
			}
		}
		
	public ApplicationsYoungBean[] getParent(int number){
			conn = connector.getConnection();
			ArrayList<ApplicationsYoungBean> result = new ArrayList<ApplicationsYoungBean>();
			
			try{
				
				StringBuffer sql = new StringBuffer();
				sql.append("select distinct a.Bby_Photo,a.Bby_Add,a.Bby_Kind,a.Bby_Money,c.Mem_NameK ");
				sql.append("from BABYPLUS a,MAPPING b,MEMBERS c ");
				sql.append("where a.Members_Id = c.ID ");
				sql.append("and a.Members_Id = b.Map_Young ");
				sql.append("and a.Members_Id = ?");
				
				String sql1 = sql.toString();
				System.out.println("들어온 번호:"+number);
				pstmt = conn.prepareStatement(sql1);
				pstmt.setInt(1,number );
				
				rs = pstmt.executeQuery();
				
				while(rs.next()){
				ApplicationsYoungBean aeBean = new ApplicationsYoungBean();
					
					aeBean.setBby_Photo(rs.getString(1));
					aeBean.setBby_Add(rs.getString(2));
					aeBean.setBby_Kind(rs.getString(3));
					aeBean.setBby_Money(rs.getString(4));
					aeBean.setMem_Nick(rs.getString(5));
					
					result.add(aeBean);
				}
			 }catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
			return result.toArray(new ApplicationsYoungBean[0]);
		}
	
	public AdminExeBean[] getChildphoto(AdminExeBean aeyBean){
		conn = connector.getConnection();
		ArrayList< AdminExeBean> result = new ArrayList<AdminExeBean>();
		try{
			
			StringBuffer sql = new StringBuffer();
			System.out.println("몇번?"+aeyBean.getMem_Id());
			sql.append( " select Bbp_Photo" );
			sql.append(	" from BABYPHOTO " );				
			sql.append( " where Bbp_Members =? " );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeyBean.getMem_Id());
			
			rs = pstmt.executeQuery();

		while(rs.next()){
			    AdminExeBean bean = new AdminExeBean();
			    bean.setBbp_Photo(rs.getString(1));
				result.add(bean);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminExeBean[0]);
	}
	
	public int choiceParent(int id){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select Map_Young" );
			sql.append(	" from MAPPING " );				
			sql.append( " where Map_Old =? " );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,id);
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				
				int young = rs.getInt(1);
				return young;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return -1;
	}

	public void finalMappingY(int check,int id){
		conn = connector.getConnection();
		try{
			
			String sql = " update MAPPING set Map_Check =?,Map_Date = Sysdate() where Map_Young=? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,check);
			pstmt.setInt(2,id);
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt,rs);
		}
	}
	
	public AdminExeBean finalYoung(AdminExeBean aeBean){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select distinct a.Mem_NameK , a.Mem_Address1,a.Mem_Address2 , a.Mem_Mobile , a.Mem_Phone ,b.Bby_Photo ,a.Mem_Email" );
			sql.append(	" from MEMBERS a, BABYPLUS b " );
			sql.append(	" where a.ID = b.Members_Id ");
			sql.append(	" and b.Members_Id = ? ");
		
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				aeBean.setMem_NameK(rs.getString(1));
				aeBean.setMem_Address1(rs.getString(2));
				aeBean.setMem_Address2(rs.getString(3));
				aeBean.setMem_Mobile(rs.getString(4));
				aeBean.setMem_Phone(rs.getString(5));
				aeBean.setBby_Photo(rs.getString(6));
				aeBean.setMem_Email(rs.getString(7));
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return  aeBean;
	}
	
	public AdminExeBean finalOld(AdminExeBean aeBean){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select distinct a.Mem_NameK , a.Mem_Address1, a.Mem_Address2 , a.Mem_Mobile , a.Mem_Phone ,b.Old_Photo,a.Mem_Email" );
			sql.append(	" from MEMBERS a, OLDPLUS b " );				
			sql.append(	" where a.ID = b.Members_Id " );
			sql.append(	" and b.Members_Id = ? " );
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeBean.getRmem_Id());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				aeBean.setMem_NameKo(rs.getString(1));
				aeBean.setMem_Address1O(rs.getString(2));
				aeBean.setMem_Address2O(rs.getString(3));
				aeBean.setMem_MoblieO(rs.getString(4));
				aeBean.setMem_PhoneO(rs.getString(5));
				aeBean.setOld_Photo(rs.getString(6));
				aeBean.setMem_EmailO(rs.getString(7));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return  aeBean;
	}
	
}
